﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Spring.Data.Generic;
using Spring.Data.Common;
using Spring.Data.Objects.Generic;
using Spring.Transaction;
using Kpmg.CompanyInfo;

namespace MySearch
{
    internal static class ServiceTemplateDAO
    {
        private static AdoTemplate tmpl = AppConfigurer.AdoTemplate;

        #region related to ServiceCategory object
        internal static IDictionary<string, ServiceCategory> ListServiceCategories()
        {
            IDictionary<string, ServiceCategory> dicRet = new Dictionary<string, ServiceCategory>();

            #region get data from service_category
            string strSel = "select cat_code, description, status from service_category ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0);
                string strDesc = dr.GetString(1);
                string strStat = dr.GetString(2);
                ServiceStatus enumStat = (ServiceStatus)Enum.Parse(typeof(ServiceStatus), strStat, true);

                dicRet.Add(strCode, new ServiceCategory(strCode, strDesc, enumStat));
            });
            #endregion

            return dicRet;
        }

        internal static void CreateServiceCategory(ServiceCategory obj)
        {
            string strIns = "insert into service_category(cat_code, description, status) "
               + " values(@code, @desc, @stat) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("code", DbType.String).Value = obj.CategoryCode;
            p.Add("desc", DbType.String).Value = obj.Description;
            p.Add("stat", DbType.String).Value = obj.Status.ToString();

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void UpdateServiceCategory(ServiceCategory obj)
        {
            string strIns = "update service_category set description=@desc, status=@stat "
               + " where cat_code=@code ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("code", DbType.String).Value = obj.CategoryCode;
            p.Add("desc", DbType.String).Value = obj.Description;
            p.Add("stat", DbType.String).Value = obj.Status.ToString();

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }
        #endregion

        #region related to ServiceType object
        internal static IDictionary<string, ServiceType> ListServiceTypes()
        {
            IDictionary<string, ServiceType> dicRet = new Dictionary<string, ServiceType>();

            #region get data from service_type
            string strSel = "select type_code, cat_code, description, status from service_type ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0);
                string strCat = dr.GetString(1);
                string strDesc = dr.GetString(2);
                string strStat = dr.GetString(3);
                ServiceStatus enumStat = (ServiceStatus)Enum.Parse(typeof(ServiceStatus), strStat, true);

                dicRet.Add(strCode, new ServiceType(strCode, strCat, strDesc, enumStat));
            });
            #endregion

            #region get data from service_type_version
            strSel = " select type_code, ver_no from service_type_version ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strCode = dr.GetString(0).Trim();
                int nVer = dr.GetInt32(1);

                if (dicRet.ContainsKey(strCode))
                    dicRet[strCode].Versions.Add(nVer);
            });
            #endregion

            return dicRet;
        }

        internal static void CreateServiceType(ServiceType obj)
        {
            string strIns = "insert into service_type(type_code, cat_code, description, status) "
               + " values(@code, @catCode, @desc, @stat) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("code", DbType.String).Value = obj.TypeCode;
            p.Add("catCode", DbType.String).Value = obj.CategoryCode;
            p.Add("desc", DbType.String).Value = obj.Description;
            p.Add("stat", DbType.String).Value = obj.Status.ToString();

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void UpdateServiceType(ServiceType obj)
        {
            string strIns = "update service_type set cat_code=@catCode, description=@desc, status=@stat "
               + " where type_code=@code ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("code", DbType.String).Value = obj.TypeCode;
            p.Add("catCode", DbType.String).Value = obj.CategoryCode;
            p.Add("desc", DbType.String).Value = obj.Description;
            p.Add("stat", DbType.String).Value = obj.Status.ToString();

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void CreateServiceTypeVersion(ServiceType obj, int verNo)
        {
            string strIns = "insert into service_type_version(type_code, ver_No) "
              + " values(@code, @verNo) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("code", DbType.String).Value = obj.TypeCode;
            p.Add("verNo", DbType.Int32).Value = verNo;

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }
        #endregion

        #region related to ServiceStep object
        internal static IDictionary<string, ServiceStep> ListServiceSteps()
        {
            IDictionary<string, ServiceStep> dicRet = new Dictionary<string, ServiceStep>();

            #region get data from service_step
            string strSel = "select step_code, type_code, step_code_user, description from service_step ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strStep = dr.GetString(0);
                string strType = dr.GetString(1);
                string strStepUser = dr.GetString(2);
                string strDesc = dr.GetString(3);

                dicRet.Add(strStep, new ServiceStep(strStep, strType, strStepUser, strDesc));
            });
            #endregion

            #region get data from service_step_version
            strSel = " select step_code, ver_no from service_step_version ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strStep = dr.GetString(0);
                int nVer = dr.GetInt32(1);

                if (dicRet.ContainsKey(strStep))
                    dicRet[strStep].Versions.Add(nVer);
            });
            #endregion

            return dicRet;
        }

        internal static void CreateServiceStep(ServiceStep obj)
        {
            string strIns = "insert into service_step(step_code, type_code, step_code_user, description) "
               + " values(@stepCode, @typeCode, @userCode, @desc) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("stepCode", DbType.String).Value = obj.StepCode;
            p.Add("typeCode", DbType.String).Value = obj.TypeCode;
            p.Add("userCode", DbType.String).Value = obj.StepCodeUser;
            p.Add("desc", DbType.String).Value = obj.Description;

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        internal static void CreateServiceStepVersion(ServiceStep obj, int verNo)
        {
            string strIns = "insert into service_step_version(step_code, ver_No) "
              + " values(@stepCode, @verNo) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("stepCode", DbType.String).Value = obj.StepCode;
            p.Add("verNo", DbType.Int32).Value = verNo;

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }
        #endregion

        #region related to ServiceQuestion object
        internal static IDictionary<string, ServiceQuestion> ListServiceQuestions()
        {
            IDictionary<string, ServiceQuestion> dicRet = new Dictionary<string, ServiceQuestion>();

            #region get data from service_step
            string strSel = "select ques_code, step_code, ques_code_user, nature, description, data_type, is_mandatory, parent_ques_code, parent_ques_answer "
                + " from service_question ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                ServiceQuestion obj = new ServiceQuestion();

                obj.QuesCode = dr.GetString(0);
                obj.StepCode = dr.GetString(1);
                obj.QuesCodeUser = dr.GetString(2);
                obj.Nature = dr.GetString(3);
                obj.Description = dr.GetString(4);
                obj.DataType = (ServiceQuestionDataType)Enum.Parse(typeof(ServiceQuestionDataType), dr.GetString(5), true);
                obj.IsMandatory = dr.GetString(6) == "Y" ? true : false;
                obj.ParentQuesCode = dr.IsDBNull(7) ? String.Empty : dr.GetString(7);
                obj.ParentQuesAnswer = dr.IsDBNull(8) ? String.Empty : dr.GetString(8);

                dicRet.Add(obj.QuesCode, obj);
            });
            #endregion

            #region get data from service_question_version
            strSel = " select ques_code, ver_no from service_question_version ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strQues = dr.GetString(0);
                int nVer = dr.GetInt32(1);

                if (dicRet.ContainsKey(strQues))
                    dicRet[strQues].Versions.Add(nVer);
            });
            #endregion

            #region get data from service_question_answer
            strSel = " select ques_code, ans_code_user, answer from service_question_answer ";
            tmpl.QueryWithRowCallbackDelegate(CommandType.Text, strSel, delegate(IDataReader dr)
            {
                string strQues = dr.GetString(0);
                string strCodeUser = dr.GetString(1);
                string strAns = dr.GetString(2);

                if (dicRet.ContainsKey(strQues))
                    dicRet[strQues].Answers.Add(strCodeUser, strAns);
            });
            #endregion

            return dicRet;
        }

        internal static void CreateServiceQuestion(ServiceQuestion obj)
        {
            string strIns = "insert into service_question(ques_code, step_code, ques_code_user, nature, description, data_type, is_mandatory, parent_ques_code, parent_ques_answer) "
               + " values(@quesCode, @stepCode, @userCode, @nature, @desc, @dataType, @isMan, @pQuesCode, @pAnswer) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("quesCode", DbType.String).Value = obj.QuesCode;
            p.Add("stepCode", DbType.String).Value = obj.StepCode;
            p.Add("userCode", DbType.String).Value = obj.QuesCodeUser;
            p.Add("nature", DbType.String).Value = obj.Nature;
            p.Add("desc", DbType.String).Value = obj.Description;
            p.Add("dataType", DbType.String).Value = obj.DataType.ToString();
            p.Add("isMan", DbType.String).Value = KpmgObject.BoolToString(obj.IsMandatory);
            p.Add("pQuesCode", DbType.String).Value = obj.ParentQuesCode;
            p.Add("pAnswer", DbType.String).Value = obj.ParentQuesAnswer;

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);

            //Insert data into service_question_answer
            strIns = "insert into service_question_answer(ques_code, ans_code_user, answer) "
                + " values(@quesCode, @userCode, @ans) ";

            foreach (KeyValuePair<string, string> kv in obj.Answers)
            {
                IDbParameters p1 = tmpl.CreateDbParameters();
                p1.Add("quesCode", DbType.String).Value = obj.QuesCode;
                p1.Add("userCode", DbType.String).Value = kv.Key;
                p1.Add("ans", DbType.String).Value = kv.Value;

                tmpl.ExecuteNonQuery(CommandType.Text, strIns, p1);
            }

        }

        internal static void CreateServiceQuestionVersion(ServiceQuestion obj, int verNo)
        {
            string strIns = "insert into service_question_version(ques_code, ver_No) "
              + " values(@quesCode, @verNo) ";

            IDbParameters p = tmpl.CreateDbParameters();
            p.Add("quesCode", DbType.String).Value = obj.QuesCode;
            p.Add("verNo", DbType.Int32).Value = verNo;

            tmpl.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        #endregion

    }
}
